﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using DBUtility;
using MyExection;
using System.Data.SqlClient;
using Model.Security.EntityInfo;
using Util;
using Model.Security.FindEntityInfo;
using Model.BusinessLogic.FindEntityInfo;
using Model.BusinessLogic.EntityInfo;

namespace BusinessLogicDAL
{
    /// <summary>
    /// 功能介绍：潜在客户数据访问层
    /// </summary>
    public class QianZaiKeHuDAL
    {
        private readonly string conStr = ConfigurationManager.AppSettings["ERPDataConnStr"];

        public QianZaiKeHuDAL()
        {
        }

        /// <summary>
        /// 创建时间：2010-7-8
        /// 功能介绍：查询DataTable信息
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        public DataTable SearchByFindEntity(FindQianZaiKeHuEntity findEntity)
        {
            try
            {
                string strSql = GetSearchSQLStrings();
                SqlParameter[] sqlParas = GetSearchParas(findEntity);
                DataTable tempDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, strSql, sqlParas);
                return tempDT;
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "ObjectDAL.SearchByFindEntity(FindQianZaiKeHuEntity  findEntity)", "查询DataTable信息");
            }
        }

        /// <summary>
        /// 创建时间:2010-7-8
        /// 功能简介:返回参数赋值组
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        private SqlParameter[] GetSearchParas(FindQianZaiKeHuEntity findEntity)
        {
            SqlParameter[] sqlParameters = { 
                    new SqlParameter("@Name",'%'+findEntity.Name+'%'),
                    new SqlParameter("@ForeignName",'%'+findEntity.ForeignName+'%'),
                 };
            return sqlParameters;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetSearchSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from TB_QianZaiKeHu where [Name] like @Name and ForeignName like @ForeignName");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-8
        /// 功能介绍：添加QianZaiKeHuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public int Add(SqlTransaction trans, QianZaiKeHuEntity entityInfo)
        {
            string strSql = GetAddSQLStrings();
            SqlParameter[] sqlParas = GetQianZaiKeHuEntityAddParas(entityInfo);
            try
            {
                return DataSwitch.ConvertObjectToInt32(DBSQLHelper.ExecuteScalar(trans, CommandType.Text, strSql, sqlParas));
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "QianZaiKeHuDAO.Add(QianZaiKeHuEntity entityInfo)", "根据实体添加数据");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetAddSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TB_QianZaiKeHu( [Name],ForeignName,[Group],Currency,FederalTaxID,UnifiedFederalTaxID,Tel1,Tel2,MobilePhone,Fax,EMail,WebSite,ShippingType,FactoringIndicator,[Password],Project,SalesEmployee,Agent,BPChannelCode,Technician,Territory,Language,AliasName,remank,AddressName,Street,Block,City,ZipCode,County,State,Country,Room) values( @Name,@ForeignName,@Group,@Currency,@FederalTaxID,@UnifiedFederalTaxID,@Tel1,@Tel2,@MobilePhone,@Fax,@EMail,@WebSite,@ShippingType,@FactoringIndicator,@Password,@Project,@SalesEmployee,@Agent,@BPChannelCode,@Technician,@Territory,@Language,@AliasName,@remank,@AddressName,@Street,@Block,@City,@ZipCode,@County,@State,@Country,@Room)");
            strSql.Append("	Select scope_identity()");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回QianZaiKeHuEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetQianZaiKeHuEntityAddParas(QianZaiKeHuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@CustomerID",entity.CustomerID),new SqlParameter("@Name",entity.Name),new SqlParameter("@ForeignName",entity.ForeignName),new SqlParameter("@Group",entity.Group),new SqlParameter("@Currency",entity.Currency),new SqlParameter("@FederalTaxID",entity.FederalTaxID),new SqlParameter("@UnifiedFederalTaxID",entity.UnifiedFederalTaxID),new SqlParameter("@Tel1",entity.Tel1),new SqlParameter("@Tel2",entity.Tel2),new SqlParameter("@MobilePhone",entity.MobilePhone),new SqlParameter("@Fax",entity.Fax),new SqlParameter("@EMail",entity.EMail),new SqlParameter("@WebSite",entity.WebSite),new SqlParameter("@ShippingType",entity.ShippingType),new SqlParameter("@FactoringIndicator",entity.FactoringIndicator),new SqlParameter("@Password",entity.Password),new SqlParameter("@Project",entity.Project),new SqlParameter("@SalesEmployee",entity.SalesEmployee),new SqlParameter("@Agent",entity.Agent),new SqlParameter("@BPChannelCode",entity.BPChannelCode),new SqlParameter("@Technician",entity.Technician),new SqlParameter("@Territory",entity.Territory),new SqlParameter("@Language",entity.Language),new SqlParameter("@AliasName",entity.AliasName),new SqlParameter("@remank",entity.remank),new SqlParameter("@AddressName",entity.AddressName),new SqlParameter("@Street",entity.Street),new SqlParameter("@Block",entity.Block),new SqlParameter("@City",entity.City),new SqlParameter("@ZipCode",entity.ZipCode),new SqlParameter("@County",entity.County),new SqlParameter("@State",entity.State),new SqlParameter("@Country",entity.Country),new SqlParameter("@Room",entity.Room)
               
                 };
            return parameter;
        }


        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-8
        /// 功能介绍：删除QianZaiKeHuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool Delete(string key)
        {
            string strSql = GetDeleteSQLStrings();
            SqlParameter[] sqlParas = GetDeleteEntityParas(key);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "QianZaiKeHuDAL.Delete(key)", "根据条件删除QianZaiKeHuEntity信息，同时返回删除结果");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetDeleteSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TB_QianZaiKeHu WHERE CustomerID= @CustomerID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回DeleteEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetDeleteEntityParas(string key)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@CustomerID",key)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-8
        /// 功能介绍：更新QianZaiKeHuEntity
        /// </summary> 
        /// <param name="entityInfo"></param>
        /// <returns></returns>      
        public bool Update(SqlTransaction trans,QianZaiKeHuEntity entityInfo)
        {
            string strSql = GetUpdateSQLStrings();
            SqlParameter[] sqlParas = GetUpdateQianZaiKeHuEntity(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "QianZaiKeHuDAL.Update(QianZaiKeHuEntity entityInfo)", "更新实体信息");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-8
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetUpdateSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TB_QianZaiKeHu SET  [Name]=@Name,ForeignName=@ForeignName,[Group]=@Group,Currency=@Currency,FederalTaxID=@FederalTaxID,UnifiedFederalTaxID=@UnifiedFederalTaxID,Tel1=@Tel1,Tel2=@Tel2,MobilePhone=@MobilePhone,Fax=@Fax,EMail=@EMail,WebSite=@WebSite,ShippingType=@ShippingType,FactoringIndicator=@FactoringIndicator,[Password]=@Password,Project=@Project,SalesEmployee=@SalesEmployee,Agent=@Agent,BPChannelCode=@BPChannelCode,Technician=@Technician,Territory=@Territory,Language=@Language,AliasName=@AliasName,remank=@remank,AddressName=@AddressName,Street=@Street,Block=@Block,City=@City,ZipCode=@ZipCode,County=@County,State=@State,Country=@Country,Room=@Room WHERE  1 = 1 AND CustomerID= @CustomerID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-8
        /// 功能简介：返回QianZaiKeHuEntity参数赋值
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetUpdateQianZaiKeHuEntity(QianZaiKeHuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@CustomerID",entity.CustomerID),new SqlParameter("@Name",entity.Name),new SqlParameter("@ForeignName",entity.ForeignName),new SqlParameter("@Group",entity.Group),new SqlParameter("@Currency",entity.Currency),new SqlParameter("@FederalTaxID",entity.FederalTaxID),new SqlParameter("@UnifiedFederalTaxID",entity.UnifiedFederalTaxID),new SqlParameter("@Tel1",entity.Tel1),new SqlParameter("@Tel2",entity.Tel2),new SqlParameter("@MobilePhone",entity.MobilePhone),new SqlParameter("@Fax",entity.Fax),new SqlParameter("@EMail",entity.EMail),new SqlParameter("@WebSite",entity.WebSite),new SqlParameter("@ShippingType",entity.ShippingType),new SqlParameter("@FactoringIndicator",entity.FactoringIndicator),new SqlParameter("@Password",entity.Password),new SqlParameter("@Project",entity.Project),new SqlParameter("@SalesEmployee",entity.SalesEmployee),new SqlParameter("@Agent",entity.Agent),new SqlParameter("@BPChannelCode",entity.BPChannelCode),new SqlParameter("@Technician",entity.Technician),new SqlParameter("@Territory",entity.Territory),new SqlParameter("@Language",entity.Language),new SqlParameter("@AliasName",entity.AliasName),new SqlParameter("@remank",entity.remank),new SqlParameter("@AddressName",entity.AddressName),new SqlParameter("@Street",entity.Street),new SqlParameter("@Block",entity.Block),new SqlParameter("@City",entity.City),new SqlParameter("@ZipCode",entity.ZipCode),new SqlParameter("@County",entity.County),new SqlParameter("@State",entity.State),new SqlParameter("@Country",entity.Country),new SqlParameter("@Room",entity.Room)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-8
        /// 功能介绍：根据“key”查询QianZaiKeHuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public QianZaiKeHuEntity GetListDetailById(string key)
        {
            DataRow row = null;
            QianZaiKeHuEntity qianZaiKeHuEntity = new QianZaiKeHuEntity();
            StringBuilder sbSqlBuilder = new StringBuilder();
            try
            {
                sbSqlBuilder.Append("SELECT * FROM TB_QianZaiKeHu WHERE TB_QianZaiKeHu.CustomerID = @CustomerID");
                string sqlStr = sbSqlBuilder.ToString();
                SqlParameter[] parameters = new SqlParameter[]{
                        new SqlParameter("@CustomerID",key)
                    };
                DataTable QianZaiKeHuDT = new DataTable();
                QianZaiKeHuDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, sqlStr, parameters);
                if ((QianZaiKeHuDT != null) && (QianZaiKeHuDT.Rows.Count > 0))
                {
                    row = QianZaiKeHuDT.Rows[0];
                }
                else
                {
                    return qianZaiKeHuEntity;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "QianZaiKeHuDAL.GetListDetailById(string key)", "获得QianZaiKeHu详细信息");
            }
            qianZaiKeHuEntity.CustomerID = DataSwitch.ConvertObjectToInt32(row["CustomerID"]);
            qianZaiKeHuEntity.Name = DataSwitch.ConvertObjectToString(row["Name"]);
            qianZaiKeHuEntity.ForeignName = DataSwitch.ConvertObjectToString(row["ForeignName"]);
            qianZaiKeHuEntity.Group = DataSwitch.ConvertObjectToString(row["Group"]);
            qianZaiKeHuEntity.Currency = DataSwitch.ConvertObjectToString(row["Currency"]);
            qianZaiKeHuEntity.FederalTaxID = DataSwitch.ConvertObjectToString(row["FederalTaxID"]);
            qianZaiKeHuEntity.UnifiedFederalTaxID = DataSwitch.ConvertObjectToString(row["UnifiedFederalTaxID"]);
            qianZaiKeHuEntity.Tel1 = DataSwitch.ConvertObjectToString(row["Tel1"]);
            qianZaiKeHuEntity.Tel2 = DataSwitch.ConvertObjectToString(row["Tel2"]);
            qianZaiKeHuEntity.MobilePhone = DataSwitch.ConvertObjectToString(row["MobilePhone"]);
            qianZaiKeHuEntity.Fax = DataSwitch.ConvertObjectToString(row["Fax"]);
            qianZaiKeHuEntity.EMail = DataSwitch.ConvertObjectToString(row["EMail"]);
            qianZaiKeHuEntity.WebSite = DataSwitch.ConvertObjectToString(row["WebSite"]);
            qianZaiKeHuEntity.ShippingType = DataSwitch.ConvertObjectToString(row["ShippingType"]);
            qianZaiKeHuEntity.FactoringIndicator = DataSwitch.ConvertObjectToString(row["FactoringIndicator"]);
            qianZaiKeHuEntity.Password = DataSwitch.ConvertObjectToString(row["Password"]);
            qianZaiKeHuEntity.Project = DataSwitch.ConvertObjectToString(row["Project"]);
            qianZaiKeHuEntity.SalesEmployee = DataSwitch.ConvertObjectToString(row["SalesEmployee"]);
            qianZaiKeHuEntity.Agent = DataSwitch.ConvertObjectToString(row["Agent"]);
            qianZaiKeHuEntity.BPChannelCode = DataSwitch.ConvertObjectToString(row["BPChannelCode"]);
            qianZaiKeHuEntity.Technician = DataSwitch.ConvertObjectToString(row["Technician"]);
            qianZaiKeHuEntity.Territory = DataSwitch.ConvertObjectToString(row["Territory"]);
            qianZaiKeHuEntity.Language = DataSwitch.ConvertObjectToString(row["Language"]);
            qianZaiKeHuEntity.AliasName = DataSwitch.ConvertObjectToString(row["AliasName"]);
            qianZaiKeHuEntity.remank = DataSwitch.ConvertObjectToString(row["remank"]);
            qianZaiKeHuEntity.AddressName = DataSwitch.ConvertObjectToString(row["AddressName"]);
            qianZaiKeHuEntity.Street = DataSwitch.ConvertObjectToString(row["Street"]);
            qianZaiKeHuEntity.Block = DataSwitch.ConvertObjectToString(row["Block"]);
            qianZaiKeHuEntity.City = DataSwitch.ConvertObjectToString(row["City"]);
            qianZaiKeHuEntity.ZipCode = DataSwitch.ConvertObjectToString(row["ZipCode"]);
            qianZaiKeHuEntity.County = DataSwitch.ConvertObjectToString(row["County"]);
            qianZaiKeHuEntity.State = DataSwitch.ConvertObjectToString(row["State"]);
            qianZaiKeHuEntity.Country = DataSwitch.ConvertObjectToString(row["Country"]);
            qianZaiKeHuEntity.Room = DataSwitch.ConvertObjectToString(row["Room"]);

            return qianZaiKeHuEntity;

        }
    }
}
